package top.callname.dao;

import java.sql.SQLException;
import java.util.ArrayList;

import org.apache.log4j.Logger;

import top.callname.bean.SignBean;
import top.callname.bean.SignRecordBean;
import top.callname.bean.SignRecordPageList;
import top.callname.bean.StudentBean;
import top.callname.bean.StudentPageList;

public class SignRecordDao extends BaseDao{

	
	Logger logger = Logger.getLogger(SignRecordDao.class);

	public boolean deleteByPrimaryKey(Integer rid){
		String sql = " DELETE FROM t_sign_record WHERE rid="+rid;
		logger.info("根据主键删除记录："+sql);
		return adu(sql);
	}
	
	/**
	 * 查询已签到的记录-不分页
	 * @param signId
	 * @return SignRecordPageList
	 */
	public ArrayList<SignRecordBean> selectSignListBySignId(Integer signId){
		ArrayList<SignRecordBean> recordList = new ArrayList<SignRecordBean>();
		String sql = "SELECT a.rid,"
				+ "a.sign_id,"
				+ "a.sign_time,"
				+ "b.stu_no,"
				+ "b.stu_name,"
				+ "c.class_name FROM "
				+ " t_sign_record a LEFT JOIN "
				+ " t_student b ON a.stu_no=b.stu_no AND  sign_id="+signId+" "
				+ "INNER JOIN t_class c ON b.cid=c.cid ";
		res = query(sql);
		try {
			while (res.next()){
				SignRecordBean bean  = new SignRecordBean();
				bean.setRid(res.getInt("rid"));
				bean.setStuNo(res.getString("stu_no"));
				bean.setSignId(res.getInt("sign_id"));
				String date = res.getString("sign_time");
				bean.setSignTime(date.substring(0,date.length()-2));
				bean.setStuName(res.getString("stu_name"));
				bean.setClassName(res.getString("class_name"));
				recordList.add(bean);
			}
			closeDB();
		} catch (SQLException e) {
			logger.error("查询 t_sign_record 明细出错："+e);
		}
		return recordList;
	}
	
	/**
	 * 查询已签到的记录-分页
	 * @param signId
	 * @return SignRecordPageList
	 */
	public SignRecordPageList selectSignListBySignId(int pageNow , Integer signId){
		SignRecordPageList pageList = new SignRecordPageList();
		ArrayList<SignRecordBean> recordList = new ArrayList<SignRecordBean>();
		String countSql = "SELECT count(1) FROM "
				+ " t_sign_record a LEFT JOIN "
				+ " t_student b ON a.stu_no=b.stu_no AND  sign_id="+signId+" "
				+ "LEFT JOIN t_class c ON b.cid=c.cid";
		int totalSize = getCountBySql(countSql);
		pageList.setPageNow(pageNow);
		pageList.setTotalSize(totalSize);
		String limit = " limit "+pageList.getStart()+","+pageList.getOffset();
		String sql = "SELECT a.rid,"
				+ "a.sign_id,"
				+ "a.sign_time,"
				+ "b.stu_no,"
				+ "b.stu_name,"
				+ "c.class_name FROM "
				+ " t_sign_record a LEFT JOIN "
				+ " t_student b ON a.stu_no=b.stu_no AND  sign_id="+signId+" "
				+ "INNER JOIN t_class c ON b.cid=c.cid "+limit;
		
		logger.info("分页查询签到明细信息："+sql);
		res = query(sql);
		try {
			while (res.next()){
				SignRecordBean bean  = new SignRecordBean();
				bean.setRid(res.getInt("rid"));
				bean.setStuNo(res.getString("stu_no"));
				bean.setSignId(res.getInt("sign_id"));
				String date = res.getString("sign_time");
				bean.setSignTime(date.substring(0,date.length()-2));
				bean.setStuName(res.getString("stu_name"));
				bean.setClassName(res.getString("class_name"));
				recordList.add(bean);
			}
			closeDB();
		} catch (SQLException e) {
			logger.error("查询 t_sign_record 明细出错："+e);
		}
		pageList.setList(recordList);
		return pageList;
	}
	

	
	/**
	 * 查询本次未签到学生 - 分页
	 * @return StudentPageList
	 */
	public StudentPageList selectNoSignList(int pageNow, SignBean signBean){
		
		StudentPageList pageList = new StudentPageList();
		
		String countSql = "SELECT count(1) FROM t_student WHERE cid IN ("+signBean.getCids()+") "
				+ "AND stu_no NOT IN ( SELECT stu_no FROM t_sign_record WHERE sign_id="+signBean.getSignId()+" )";
		
		//查询总记录
		int totalSize = getCountBySql(countSql);
		pageList.setPageNow(pageNow);
		pageList.setTotalSize(totalSize);
		
		ArrayList<StudentBean> list = new ArrayList<StudentBean>();
		
		String limit = " limit "+pageList.getStart()+","+pageList.getOffset();
		String sql =  "select  a.sid,a.cid,a.stu_no,a.stu_name,a.openid,a.createtime,b.class_name "
				+ "  FROM t_student as a INNER JOIN t_class b ON a.cid=b.cid  AND a.cid IN ("+signBean.getCids()+") "
				+ "AND a.stu_no NOT IN ( SELECT stu_no FROM t_sign_record "
				+ "WHERE sign_id="+signBean.getSignId()+" ) " +limit;
		logger.info("分页查询本次未签到学生信息："+sql);
		res = query(sql);
		try {
			while(res.next()){
				StudentBean bean = new StudentBean();
				bean.setSid(res.getInt("sid"));
				bean.setCid(res.getInt("cid"));
				bean.setStuName(res.getString("stu_name"));
				bean.setStuNo(res.getString("stu_no"));
				bean.setOpenid(res.getString("openid"));
				bean.setClassName(res.getString("class_name"));
				String date = res.getString("createtime");
				bean.setCreatetime(date.substring(0,date.length()-2));
				list.add(bean);
			}
			closeDB();
		} catch (SQLException e) {
			logger.error("查询 t_student 出错："+e);
		}
		pageList.setList(list);
		return pageList;
	}

	
	/**
	 * 查询本次未签到学生 - 不分页
	 * @return ArrayList<StudentBean>
	 */
	public ArrayList<StudentBean>  selectNoSignList(SignBean signBean){
		ArrayList<StudentBean> list = new ArrayList<StudentBean>();
		String sql =  "select  a.sid,a.cid,a.stu_no,a.stu_name,a.openid,a.createtime,b.class_name "
				+ "  FROM t_student as a INNER JOIN t_class b ON a.cid=b.cid  AND a.cid IN ("+signBean.getCids()+") "
				+ "AND a.stu_no NOT IN ( SELECT stu_no FROM t_sign_record "
				+ "WHERE sign_id="+signBean.getSignId()+" ) ";
		logger.info("导出数据查询本次未签到学生信息："+sql);
		res = query(sql);
		try {
			while(res.next()){
				StudentBean bean = new StudentBean();
				bean.setSid(res.getInt("sid"));
				bean.setCid(res.getInt("cid"));
				bean.setStuName(res.getString("stu_name"));
				bean.setStuNo(res.getString("stu_no"));
				bean.setOpenid(res.getString("openid"));
				bean.setClassName(res.getString("class_name"));
				String date = res.getString("createtime");
				bean.setCreatetime(date.substring(0,date.length()-2));
				list.add(bean);
			}
			closeDB();
		} catch (SQLException e) {
			logger.error("查询 t_student 出错："+e);
		}
		return list;
	}
	
	/**
	 * 新增记录
	 * @param stuNo
	 * @param openid
	 * @return boolean
	 */
	public boolean addSignRecord(SignRecordBean signRecord){
		String sql = "insert into t_sign_record (sign_id,stu_no,sign_time) "
				+ "values ( '"
						+signRecord.getSignId()+"','"
						+signRecord.getStuNo()+"',"
								+ "NOW() )";
		logger.info("新增签到记录详细表："+sql);
		return adu(sql);
	}
	
	public SignRecordBean selectByStuNoAndSignId(String stuNo,Integer signId){
		SignRecordBean bean = null;
		String sql =  "select  rid,stu_no,sign_id,sign_time"
				+ "  from t_sign_record where sign_id="+signId+" and stu_no='"+stuNo+"'";
		logger.info("根据stuNo和signId 查询学生签到信息："+sql);
		res = query(sql);
		try {
			if (res.next()){
				bean = new SignRecordBean();
				bean.setRid(res.getInt("rid"));
				bean.setStuNo(res.getString("stu_no"));
				bean.setSignId(res.getInt("sign_id"));
				String date = res.getString("sign_time");
				bean.setSignTime(date.substring(0,date.length()-2));
			}
			closeDB();
		} catch (SQLException e) {
			logger.error("查询 t_sign_record 出错："+e);
		}
		return bean;
	}
	
}
